/*
	This Procedure loads all the Running, Hold and Shipped Lots Inventory Cost in Chiptrac.

 	EXEC sp_inventory_summary_cost_report_proc '2399','0','0','0','0','0','7/8/2007:19:57:39'

	Need to activate the commented condition for Lot Category when lot category in lot table is not null
*/

alter  PROC sp_inventory_summary_cost_report_proc

	@PRODUCT_ID 	VARCHAR(200),
	@VENDOR		VARCHAR(50),
	@ASOFDATE	VARCHAR(50),
	@STATUS		VARCHAR(50),
	@LOT_CATEGORY	VARCHAR(50),
	@REPORT_TYPE	VARCHAR(20),
	@DEFAULT_PROMPT	VARCHAR(50)

AS
BEGIN
	CREATE TABLE #INVENTORY_SUMMARY_TABLE ( FAB_LOT_NUMBER VARCHAR(50),LOT_NUMBER VARCHAR(50),WAFER_QTY_IN NUMERIC(18,5),QTY_IN NUMERIC(18,5),
					PART_NUMBER VARCHAR(50), PROCESSSTEP_ID INT,BASE_PRODUCT VARCHAR(50),PS_TYPE VARCHAR(50))

	DECLARE @RESULTS TABLE ( 
							BASE_PRODUCT VARCHAR(50),
							PART_NUMBER VARCHAR(50),
							QTY_1 NUMERIC(18,5), /*Fab*/
							QTY_2 NUMERIC(18,5), /*WAFER BANK*/
							QTY_3 NUMERIC(18,5), /*SORT*/
							QTY_4 NUMERIC(18,5), /*DIE BANK*/
							QTY_5 NUMERIC(18,5), /*ASSEMBLY*/
							QTY_6 NUMERIC(18,5), /*FT*/
							QTY_7 NUMERIC(18,5), /*ReTest*/
							QTY_8 NUMERIC(18,5), /*QC*/
							QTY_9 NUMERIC(18,5), /*Ball Scan*/
							QTY_10 NUMERIC(18,5),/*Baking*/
							QTY_11 NUMERIC(18,5) /*FG*/
						)

	DECLARE @SQLSTRING 			NVARCHAR(4000)

	DECLARE @BASE_PRODUCT		VARCHAR(50)
	DECLARE @PART_NUMBER 		VARCHAR(50)
	DECLARE @PS_TYPE 			VARCHAR(50)
	DECLARE @PACKAGE_TYPE 		VARCHAR(50)
	DECLARE @WAFER_QTY_IN 		NUMERIC(18,5)
	DECLARE @QTY_IN 			NUMERIC(18,5)

	DECLARE @QTY_1 				NUMERIC(18,5)	/*Fab*/       
	DECLARE @QTY_2 				NUMERIC(18,5)	/*WAFER BANK*/
	DECLARE @QTY_3				NUMERIC(18,5)	/*SORT*/      
	DECLARE @QTY_4 				NUMERIC(18,5)	/*DIE BANK*/  
	DECLARE @QTY_5 				NUMERIC(18,5)	/*ASSEMBLY*/  
	DECLARE @QTY_6				NUMERIC(18,5)	/*FT*/        
	DECLARE @QTY_7				NUMERIC(18,5)	/*ReTest*/    
	DECLARE @QTY_8				NUMERIC(18,5)	/*QC*/        
	DECLARE @QTY_9				NUMERIC(18,5)	/*Ball Scan*/ 
	DECLARE @QTY_10				NUMERIC(18,5)	/*Baking*/    
	DECLARE @QTY_11				NUMERIC(18,5)	/*FG*/        

	DECLARE @TEMP_VARCHAR 		VARCHAR(50)
	DECLARE @START_DATE 		DATETIME
	DECLARE @END_DATE 			DATETIME

	DECLARE @LOT_CURSOR 		CURSOR


	SET @SQLSTRING = N'INSERT INTO #INVENTORY_SUMMARY_TABLE
		SELECT  FAB_LOT_NUMBER, VENDOR_LOT_NUMBER, ( ISNULL(LOT_CUMMULATIVE_COST,0) ), ( ISNULL(LOT_CUMMULATIVE_COST,0) ),--LOT_CATEGORY,L_VENDOR_NAME, 
		PART_NUMBER,P.PROCESSSTEP_ID,
		(SELECT PART_NUMBER FROM PRODUCT WHERE PRODUCT_ID = 
			CASE 
				WHEN ( P.P_PARENT_PRODUCT_ID IS NULL OR P.P_PARENT_PRODUCT_ID = -1 ) THEN P.PRODUCT_ID 
				WHEN P.P_PARENT_PRODUCT_ID IS NOT NULL THEN P.P_PARENT_PRODUCT_ID	
			END 
		) AS BASE_PRODUCT,PS_TYPE '
	
	IF @ASOFDATE = '0'
	BEGIN
		SET @SQLSTRING = @SQLSTRING + '	FROM LOT_TABLE LT,PRODUCT P, PROCESS_STEP PS, LOCATION L '
	END
	ELSE
	BEGIN
		SET @SQLSTRING = @SQLSTRING + '	FROM LOT_TABLE_HISTORY LT,PRODUCT P, PROCESS_STEP PS, LOCATION L '

		SET @TEMP_VARCHAR = CONVERT(VARCHAR, @ASOFDATE, 101)+' 12:00AM'
		SET @START_DATE = CONVERT(DATETIME, @TEMP_VARCHAR)
		SET @TEMP_VARCHAR = NULL
		SET @TEMP_VARCHAR = CONVERT(VARCHAR, @ASOFDATE, 101)+' 11:59PM'
		SET @END_DATE = CONVERT(DATETIME, @TEMP_VARCHAR)
	END
		SET @SQLSTRING = @SQLSTRING + ' WHERE 
		LT.PROCESSSTEP_ID = P.PROCESSSTEP_ID
		AND P.PROCESSSTEP_ID = PS.PROCESSSTEP_ID
		AND LT.LOCATION_ID = L.LOCATION_ID
		AND P_TYPE IS NULL
		AND P.OBSOLETE != 0 '

	IF @ASOFDATE <> '0'
	BEGIN
		SET @SQLSTRING = @SQLSTRING + ' AND LT.TimeStamp = ( SELECT MAX(LTH.Timestamp) FROM LOT_TABLE_HISTORY LTH WHERE LTH.LOT_ID = LT.LOT_ID AND LTH.TIMESTAMP BETWEEN '''+ CONVERT(VARCHAR,@START_DATE)+''''+' AND '''+ CONVERT(VARCHAR,@END_DATE)+''') '
	END

	IF @PRODUCT_ID <> '0'
	BEGIN
		SET @SQLSTRING = @SQLSTRING + ' AND P.P_PARENT_PRODUCT_ID in ( SELECT PROCESSSTEP_ID FROM FN_GET_PROCESS_STEPS_IN_STRING ('''+ @PRODUCT_ID +'''))'
	END
	IF @LOT_CATEGORY <> '0'
	BEGIN
		SET @SQLSTRING = @SQLSTRING + ' AND ISNULL(LT.LOT_CATEGORY, ''PROD'') =  ''' +  @LOT_CATEGORY + '''' 
		--SET @SQLSTRING = @SQLSTRING + ' AND LT.LOT_CATEGORY =  ''' +  @LOT_CATEGORY + '''' 
	END 
	IF @VENDOR <> '0'
	BEGIN
		SET @SQLSTRING = @SQLSTRING + ' AND L.VENDOR_ID =  ''' +  @VENDOR + '''' 
	END
	IF @STATUS <> '0'
	BEGIN
		SET @SQLSTRING = @SQLSTRING + ' AND LT.LOT_CURRENT_STATUS =  ''' +  @STATUS + '''' 
	END
	ELSE
	BEGIN
		SET @SQLSTRING = @SQLSTRING + ' AND ( LOT_CURRENT_STATUS = ''RUNNING'' OR LOT_CURRENT_STATUS = ''HOLD'' )' 
	END	
		
	--PRINT @SQLSTRING
		
	EXEC(@SQLSTRING)
	
	INSERT INTO @RESULTS ( BASE_PRODUCT,PART_NUMBER ) (SELECT DISTINCT BASE_PRODUCT,PART_NUMBER FROM #INVENTORY_SUMMARY_TABLE )

	SET @LOT_CURSOR = CURSOR FOR 

			SELECT 
				SUM(WAFER_QTY_IN),SUM(QTY_IN),PART_NUMBER,BASE_PRODUCT,PS_TYPE
			from 
				#INVENTORY_SUMMARY_TABLE
			GROUP BY 
				BASE_PRODUCT,PART_NUMBER,PS_TYPE

	OPEN @LOT_CURSOR
	FETCH NEXT FROM @LOT_CURSOR INTO @WAFER_QTY_IN,@QTY_IN,@PART_NUMBER,@BASE_PRODUCT,@PS_TYPE
	WHILE @@FETCH_STATUS = 0
	BEGIN
		IF ( @PS_TYPE = 'FAB')
		BEGIN
			SET @QTY_1 = @WAFER_QTY_IN
			--SET @DIE_TOTAL_FOR_WAFERS = ( @WAFER_QTY_IN * ISNULL(@GROSS_DIE,0) )								
			UPDATE @RESULTS SET QTY_1 = @QTY_1--,FAB_DIE_QTY = @DIE_TOTAL_FOR_WAFERS
			WHERE PART_NUMBER = @PART_NUMBER AND BASE_PRODUCT = @BASE_PRODUCT
		END
		ELSE IF ( @PS_TYPE = 'WAFER BANK')
		BEGIN
			SET @QTY_2 = @WAFER_QTY_IN
			--SET @DIE_TOTAL_FOR_WAFERS = ( @WAFER_QTY_IN * ISNULL(@GROSS_DIE,0) )
								
			UPDATE @RESULTS SET QTY_2 = @QTY_2--,FAB_OPTION_DIE_QTY = @DIE_TOTAL_FOR_WAFERS
			WHERE PART_NUMBER = @PART_NUMBER AND BASE_PRODUCT = @BASE_PRODUCT
		END
		ELSE IF ( @PS_TYPE = 'SORT')
		BEGIN
			SET @QTY_3 = @WAFER_QTY_IN
			--SET @DIE_TOTAL_FOR_WAFERS = ( @WAFER_QTY_IN * ISNULL(@GROSS_DIE,0) )
								
			UPDATE @RESULTS SET QTY_3 = @QTY_3--,FAB_OPTION_DIE_QTY = @DIE_TOTAL_FOR_WAFERS
			WHERE PART_NUMBER = @PART_NUMBER AND BASE_PRODUCT = @BASE_PRODUCT
		END
		ELSE IF ( @PS_TYPE = 'Die Bank')
		BEGIN
			SET @QTY_4 = @QTY_IN
			
			UPDATE @RESULTS SET QTY_4 = @QTY_4 WHERE PART_NUMBER = @PART_NUMBER AND BASE_PRODUCT = @BASE_PRODUCT
		END
		ELSE IF ( @PS_TYPE = 'Assembly')
		BEGIN
			SET @QTY_5 = @QTY_IN

			UPDATE @RESULTS SET QTY_5 = @QTY_5 WHERE PART_NUMBER = @PART_NUMBER AND BASE_PRODUCT = @BASE_PRODUCT			
		END
		ELSE IF ( @PS_TYPE = 'FT')
		BEGIN
			SET @QTY_6 = @QTY_IN

			UPDATE @RESULTS SET QTY_6 = @QTY_6 WHERE PART_NUMBER = @PART_NUMBER AND BASE_PRODUCT = @BASE_PRODUCT			
		END
		ELSE IF ( @PS_TYPE = 'ReTest')
		BEGIN
			SET @QTY_7 = @QTY_IN

			UPDATE @RESULTS SET QTY_7 = @QTY_7 WHERE PART_NUMBER = @PART_NUMBER AND BASE_PRODUCT = @BASE_PRODUCT			
		END
		ELSE IF ( @PS_TYPE = 'QC')
		BEGIN
			SET @QTY_8 = @QTY_IN
			
			UPDATE @RESULTS SET QTY_8 = @QTY_8 WHERE PART_NUMBER = @PART_NUMBER AND BASE_PRODUCT = @BASE_PRODUCT
		END
		ELSE IF ( @PS_TYPE = 'Ball Scan')
		BEGIN
			SET @QTY_9 = @QTY_IN
			
			UPDATE @RESULTS SET QTY_9 = @QTY_9 WHERE PART_NUMBER = @PART_NUMBER AND BASE_PRODUCT = @BASE_PRODUCT
		END
		ELSE IF ( @PS_TYPE = 'Baking')
		BEGIN
			SET @QTY_10 = @QTY_IN
			
			UPDATE @RESULTS SET QTY_10 = @QTY_10 WHERE PART_NUMBER = @PART_NUMBER AND BASE_PRODUCT = @BASE_PRODUCT
		END
		ELSE IF ( @PS_TYPE = 'Baking')
		BEGIN
			SET @QTY_11 = @QTY_IN
			
			UPDATE @RESULTS SET QTY_11 = @QTY_11 WHERE PART_NUMBER = @PART_NUMBER AND BASE_PRODUCT = @BASE_PRODUCT
		END
			
		SET @QTY_1 = NULL
		SET @QTY_2 = NULL
		SET @QTY_3 = NULL
		SET @QTY_4 = NULL
		SET @QTY_5 = NULL
		SET @QTY_6 = NULL
		SET @QTY_7 = NULL
		SET @QTY_8 = NULL
		SET @QTY_9 = NULL
		SET @QTY_10 = NULL
		SET @QTY_11 = NULL


		SET @BASE_PRODUCT	= NULL
		SET @PART_NUMBER 	= NULL
		SET @WAFER_QTY_IN 	= NULL
		SET @QTY_IN 		= NULL
		SET @PS_TYPE 		= NULL

		FETCH NEXT FROM @LOT_CURSOR INTO @WAFER_QTY_IN, @QTY_IN, @PART_NUMBER, @BASE_PRODUCT, @PS_TYPE
	END
 	CLOSE @LOT_CURSOR
 	DEALLOCATE @LOT_CURSOR

	SELECT 
		BASE_PRODUCT, PART_NUMBER, QTY_1, QTY_2, QTY_3, QTY_4, QTY_5, QTY_6, QTY_7, QTY_8, QTY_9, QTY_10, QTY_11
	FROM @RESULTS 
	ORDER BY BASE_PRODUCT,PART_NUMBER
END
